package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.PageHandler;
import com.glowdayz.common.MultipartBox.FileInfo;
import com.glowdayz.glowmee.cmd.ReviewCommentCMD;

/** 제품 덧글의 기능을 구현하는 클래스 */
public class ReviewCommentDAO {
	
static Logger logger = Logger.getLogger(ReviewCommentDAO.class);
	
	private static ReviewCommentDAO instance = null;
	
	DBHandler db = null;
	
	public static ReviewCommentDAO getInstance() {
		if (instance == null) {
			instance = new ReviewCommentDAO();
		}
		return instance;
	}
	
	public ReviewCommentDAO() {
		db = DBHandler.getInstance();
	}
	
	/**
	 * 제품 덧글 전시여부
	 * @param cmd
	 * @return int
	 * @throws SQLException 
	 */
	public int unDisplayReviewComment(ReviewCommentCMD cmd) throws SQLException {
		logger.debug("[unDisplayReviewComment] ---------- Method Start ----------");
		logger.debug("[unDisplayReviewComment][PARAM] ReviewCommentCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE reviewcomment "
				+ "SET isDisplay=? "
				+ "WHERE idRegister=? AND idProduct=?";
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIsDisplay());
			pstmt.setInt(2, cmd.getIdRegister());
			pstmt.setInt(3, cmd.getIdProduct());
			
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[unDisplayReviewComment] " + e.getLocalizedMessage());
			logger.error("[unDisplayReviewComment] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		int count = db.getCount("reviewcomment", "*", "idProduct=" + cmd.getIdProduct() + " AND isDisplay=1");
		int ratingSum = db.getSum("reviewcomment", "rating", "idProduct=" + cmd.getIdProduct() + " AND isDisplay=1");
		
		String add = null;
		if (cmd.getIsDisplay() == 1) {
			add = "+1";
		} else if (cmd.getIsDisplay() == 0) {
			add = "-1";
		}
		
		String update = "UPDATE product "
				+ "SET ratingAvg=?, rationCount=(rationCount" + add + "), modified_date=? "
				+ "WHERE idProduct=?";
		
		pstmt = db.getPrepareStatement(update);
		
		try {
			if (count == 0 && cmd.getIsDisplay() == 0) {
				pstmt.setDouble(1, 0);
			} else {
				pstmt.setDouble(1, ((double) ratingSum) / ((double) count));
			}
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(3, cmd.getIdProduct());
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[addReviewProduct] " + e.getLocalizedMessage());
			logger.error("[addReviewProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[unDisplayReviewComment][RETURN] result=" + result);
		logger.debug("[unDisplayReviewComment] ---------- Method End ----------");
		
		return result;
	}
	
	/** 리뷰 작성 xml 
	 * @throws SQLException */
	public int addReviewProduct(int idRegister, int idProduct, String reviewText, int rating) throws SQLException {
		logger.debug("[addReviewProduct] ---------- Method Start ----------");
		
		int result = 0;
		
		PreparedStatement pstmt = null;
		
		DBHandler.getInstance().beginTrans();
		
		String sql = "INSERT INTO reviewcomment("
				+ "idRegister, idProduct, reviewText, rating, isDisplay, create_date"
				+ ") VALUES (?, ?, ?, ?, 1, ?)";
		
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idRegister);
			pstmt.setInt(2, idProduct);
			pstmt.setString(3, reviewText);
			pstmt.setInt(4, rating);
			pstmt.setString(5, DateTime.getInstance().getNowDateTimeString());
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[addReviewProduct] " + e.getLocalizedMessage());
			logger.error("[addReviewProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		int count = db.getCount("reviewcomment", "*", "idProduct=" + idProduct + " AND isDisplay=1");
		int ratingSum = db.getSum("reviewcomment", "rating", "idProduct=" + idProduct + " AND isDisplay=1");
		
		String update = "UPDATE product "
				+ "SET ratingAvg=?, rationCount=(rationCount+1), modified_date=? "
				+ "WHERE idProduct=?";
		
		pstmt = db.getPrepareStatement(update);
		
		double ratingResult = ((double) ratingSum) / ((double) count);
		ratingResult = Math.round(ratingResult * 100.0) / 100.0;
		
		try {
			pstmt.setDouble(1, ratingResult);
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(3, idProduct);
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[addReviewProduct] " + e.getLocalizedMessage());
			logger.error("[addReviewProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		int readCnt = 0, ratingCnt = 0, wishCnt = 0, pouchCnt = 0;
		double ratingAvg = 0.0;
		String selectSql = "SELECT p.readCount, p.ratingAvg, p.rationCount, "
				+ "(SELECT COUNT(*) FROM wishlistmapping m WHERE m.idProduct=p.idProduct), "
				+ "(SELECT COUNT(*) FROM pouchproductmapping pm WHERE pm.idProduct=p.idProduct) "
				+ "FROM product p "
				+ "WHERE p.idProduct=?;";
		pstmt = DBHandler.getInstance().getPrepareStatement(selectSql);
		try {
			pstmt.setInt(1, idProduct);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					readCnt = rs.getInt(1);
					ratingAvg = rs.getDouble(2);
					ratingCnt = rs.getInt(3);
					wishCnt = rs.getInt(4);
					pouchCnt = rs.getInt(5);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[getEditerPouch] " + e.getLocalizedMessage());
			logger.error("[getEditerPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		int productScore = (readCnt * 1) + ((int) (ratingAvg * ratingCnt)) + (wishCnt * 5) + (pouchCnt * 10);
		
		String updateSql = "UPDATE product "
				+ "SET productScore=? "
				+ "WHERE idProduct=?;";
		
		pstmt = db.getPrepareStatement(updateSql);
		
		try {
			pstmt.setInt(1, productScore);
			pstmt.setInt(2, idProduct);
			result = pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[wishListPouch] " + e.getLocalizedMessage());
			logger.error("[wishListPouch] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[addReviewProduct][RETURN] result=" + result);
		logger.debug("[addReviewProduct] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 제품 리뷰덧글 리스트
	 * @param page
	 * @param count
	 * @param order
	 * @param where
	 * @return ArrayList<ReviewCommentCMD>
	 */
	public ArrayList<ReviewCommentCMD> getReviewCommentList(int page, int count, String order, String where) throws Exception {
		logger.debug("[getReviewCommentList] ---------- Method Start ----------");
		logger.debug("[getReviewCommentList][PARAM] page=" + page);
		logger.debug("[getReviewCommentList][PARAM] count=" + count);
		logger.debug("[getReviewCommentList][PARAM] order=" + order);
		logger.debug("[getReviewCommentList][PARAM] where=" + where);
		
		ArrayList<ReviewCommentCMD> result = null;
		int cnt;
		if (where == null) {
			cnt = db.getCount("reviewcomment c, register r, product p, brand b", "c.create_date", 
					"c.idRegister=r.idRegister AND c.idProduct=p.idProduct AND p.idBrand=b.idBrand");
		} else {
			cnt = db.getCount("reviewcomment c, register r, product p, brand b", "c.create_date", 
					"c.idRegister=r.idRegister AND c.idProduct=p.idProduct AND p.idBrand=b.idBrand" + where);
		}
		
		
		String sql = null;
		if (order == null && where == null) {
			sql = "SELECT c.idRegister, p.idBrand, b.brandTitle, c.idProduct, p.productTitle, r.nickName, c.rating, c.reviewText, "
					+ "c.create_date, c.isDisplay "
					+ "FROM reviewcomment c, register r, product p, brand b "
					+ "WHERE c.idRegister=r.idRegister AND c.idProduct=p.idProduct AND p.idBrand=b.idBrand "
					+ "ORDER BY c.create_date DESC "
					+ "LIMIT ?, ?";
		} else if (order != null && where != null) {
			sql = "SELECT c.idRegister, p.idBrand, b.brandTitle, c.idProduct, p.productTitle, r.nickName, c.rating, c.reviewText, "
					+ "c.create_date, c.isDisplay "
					+ "FROM reviewcomment c, register r, product p, brand b "
					+ "WHERE c.idRegister=r.idRegister AND c.idProduct=p.idProduct AND p.idBrand=b.idBrand" + where + " "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (order != null) {
			sql = "SELECT c.idRegister, p.idBrand, b.brandTitle, c.idProduct, p.productTitle, r.nickName, c.rating, c.reviewText, "
					+ "c.create_date, c.isDisplay "
					+ "FROM reviewcomment c, register r, product p, brand b "
					+ "WHERE c.idRegister=r.idRegister AND c.idProduct=p.idProduct AND p.idBrand=b.idBrand "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (where != null) {
			sql = "SELECT c.idRegister, p.idBrand, b.brandTitle, c.idProduct, p.productTitle, r.nickName, c.rating, c.reviewText, "
					+ "c.create_date, c.isDisplay "
					+ "FROM reviewcomment c, register r, product p, brand b "
					+ "WHERE c.idRegister=r.idRegister AND c.idProduct=p.idProduct AND p.idBrand=b.idBrand" + where + " "
					+ "ORDER BY c.create_date DESC "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<ReviewCommentCMD>();
				
				while(rs.next()) {
					int idRegister = rs.getInt(1);
					int idBrand = rs.getInt(2);
					String brandTitle = rs.getString(3);
					int idProduct = rs.getInt(4);
					String productTitle = rs.getString(5);
					String nickName = rs.getString(6);
					int rating = rs.getInt(7);
					String reviewText = rs.getString(8);
					String create_date = rs.getString(9);
					int isDisplay = rs.getInt(10);
					ReviewCommentCMD item = new ReviewCommentCMD(idRegister, nickName, idBrand, brandTitle, idProduct, productTitle, 
							reviewText, rating, isDisplay, create_date);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getReviewCommentList] " + e.getLocalizedMessage());
			logger.error("[getReviewCommentList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getReviewCommentList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getReviewCommentList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 리뷰 리스트 xml */
	public ArrayList<ReviewCommentCMD> getReviewCommentList(int page, int count, int idProduct, String order, String where) throws Exception {
		logger.debug("[getReviewCommentList] ---------- Method Start ----------");
		
		ArrayList<ReviewCommentCMD> result = null;
		int cnt = db.getCount("reviewcomment c, product p", "*", 
					"c.idProduct=p.idProduct AND p.isDisplay=1 AND c.isDisplay=1 AND c.idProduct=" + idProduct);
		
		String sql = "SELECT c.idRegister, r.nickName, r.birthYear, s.commonText, c.rating, c.reviewText, c.create_date "
				+ "FROM reviewcomment c LEFT OUTER JOIN register r ON c.idRegister=r.idRegister "
				+ "LEFT OUTER JOIN product p ON p.isDisplay=1 "
				+ "LEFT OUTER JOIN commoncode s ON r.skinType=s.idCommon "
				+ "WHERE c.isDisplay=1 AND c.idProduct=? "
				+ "GROUP BY c.idRegister "
				+ "ORDER BY c.create_date DESC "
				+ "LIMIT ?, ?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, idProduct);
			pstmt.setInt(2, PageHandler.getInstance().getStartRec());
			pstmt.setInt(3, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<ReviewCommentCMD>();
				while(rs.next()) {
					int idRegister = rs.getInt(1);
					String nickName = rs.getString(2);
					int birthYear = rs.getInt(3);
					String commonText = rs.getString(4);
					int rating = rs.getInt(5);
					String reviewText = rs.getString(6);
					String create_date = rs.getString(7);
					
					FileInfo image = this.getImage(idRegister);
					
					ReviewCommentCMD item = new ReviewCommentCMD(idRegister, nickName, reviewText, rating, create_date, birthYear, commonText, image);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getReviewCommentList] " + e.getLocalizedMessage());
			logger.error("[getReviewCommentList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getReviewCommentList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getReviewCommentList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 리뷰 리스트 xml */
	public ArrayList<ReviewCommentCMD> getReviewCommentProduct(int page, int count, String order, String where) throws Exception {
		logger.debug("[getReviewCommentProduct] ---------- Method Start ----------");
		logger.debug("[getReviewCommentProduct][PARAM] order=" + order);
		logger.debug("[getReviewCommentProduct][PARAM] where=" + where);
		
		ArrayList<ReviewCommentCMD> result = null;
		int cnt = db.getCount("(SELECT c.idRegister, r.nickName, r.birthYear, s.commonText, c.rating, c.reviewText, c.create_date, "
					+ "p.idBrand, b.brandTitle, c.idProduct, p.productTitle "
					+ "FROM reviewcomment c LEFT OUTER JOIN register r ON c.idRegister=r.idRegister "
					+ "LEFT OUTER JOIN product p ON p.isDisplay=1 AND p.idProduct=c.idProduct "
					+ "LEFT OUTER JOIN commoncode s ON r.skinType=s.idCommon "
					+ "LEFT OUTER JOIN brand b ON b.isDisplay=1 AND p.idBrand=b.idBrand "
					+ "LEFT OUTER JOIN secondcategorymapping m ON m.idPRoduct=p.idProduct "
					+ "WHERE c.isDisplay=1" + where + " "
					+ "GROUP BY c.idRegister, c.idProduct) tbl", "*", null);
		
		String sql = null;
		if (order == null) {
			sql = "SELECT c.idRegister, r.nickName, r.birthYear, s.commonText, c.rating, c.reviewText, c.create_date, "
					+ "p.idBrand, b.brandTitle, c.idProduct, p.productTitle "
					+ "FROM reviewcomment c LEFT OUTER JOIN register r ON c.idRegister=r.idRegister "
					+ "LEFT OUTER JOIN product p ON p.isDisplay=1 AND p.idProduct=c.idProduct "
					+ "LEFT OUTER JOIN commoncode s ON r.skinType=s.idCommon "
					+ "LEFT OUTER JOIN brand b ON b.isDisplay=1 AND p.idBrand=b.idBrand "
					+ "LEFT OUTER JOIN secondcategorymapping m ON m.idPRoduct=p.idProduct "
					+ "WHERE c.isDisplay=1" + where + " "
					+ "GROUP BY c.idRegister, c.idProduct "
					+ "ORDER BY c.create_date DESC "
					+ "LIMIT ?, ?";
		} else if (order != null) {
			sql = "SELECT c.idRegister, r.nickName, r.birthYear, s.commonText, c.rating, c.reviewText, c.create_date, "
					+ "p.idBrand, b.brandTitle, c.idProduct, p.productTitle "
					+ "FROM reviewcomment c LEFT OUTER JOIN register r ON c.idRegister=r.idRegister "
					+ "LEFT OUTER JOIN product p ON p.isDisplay=1 AND p.idProduct=c.idProduct "
					+ "LEFT OUTER JOIN commoncode s ON r.skinType=s.idCommon "
					+ "LEFT OUTER JOIN brand b ON b.isDisplay=1 AND p.idBrand=b.idBrand "
					+ "LEFT OUTER JOIN secondcategorymapping m ON m.idPRoduct=p.idProduct "
					+ "WHERE c.isDisplay=1" + where + " "
					+ "GROUP BY c.idRegister, c.idProduct "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<ReviewCommentCMD>();
				while(rs.next()) {
					int idRegister = rs.getInt(1);
					String nickName = rs.getString(2);
					int birthYear = rs.getInt(3);
					String commonText = rs.getString(4);
					int rating = rs.getInt(5);
					String reviewText = rs.getString(6);
					String create_date = rs.getString(7);
					int idBrand = rs.getInt(8);
					String brandTitle = rs.getString(9);
					int idProduct = rs.getInt(10);
					String productTitle = rs.getString(11);
					
					FileInfo image = this.getImage(idProduct, "product");
					FileInfo userImg = this.getImage(idRegister, "register");
					
					ReviewCommentCMD item = new ReviewCommentCMD(idRegister, nickName, idBrand, brandTitle, 
							idProduct, productTitle, reviewText, rating, create_date, birthYear, commonText, image, userImg);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getReviewCommentProduct] " + e.getLocalizedMessage());
			logger.error("[getReviewCommentProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getReviewCommentProduct][RETURN] result_count=" + result.size());
		}
		logger.debug("[getReviewCommentProduct] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 이미지
	 * @param idx
	 * @return FileInfo
	 */
	public FileInfo getImage(int idx) throws SQLException {
		String fileSql;
		
		if (idx == -1) {
			fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM attachefile "
					+ "WHERE fileSaveName='noimage.png' AND fileDir= '/upload'";
		} else {
			fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM attachefile "
					+ "WHERE tableName='register' AND tableKey=" + idx;
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo image = null;
		
		try {
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					image = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return image;
	}
	
	public FileInfo getImage(int idx, String table) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
				+ "FROM attachefile "
				+ "WHERE tableName='" + table + "' AND tableKey=? ORDER BY sortKey LIMIT 0, 1";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
}
